package server;

// 딴거에서 쓰던 DB연결 관련 메소드(잔뜩 수정해야됨...)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Query {
	
	Connection c;
	String dbName = "multiserver";
	String id = "root";
	String pw = "autoset";
	String tableName = "userdb";
	String dbserver = "jdbc:mysql://localhost:3306/";
	String url;
	
	public static void main(String[] args) {
		String posData[][];
		// for test
		String sql = "SELECT posx, posy FROM posdata WHERE username ='aaa';";
		
		Query q = new Query();
		q.setacc("root", "autoset");
		q.usedb("onlinegame");
		q.usetable("posdata");
		q.connect();
		
		posData = q.query_str(sql);
	}
	
	public Query() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		url = dbserver + dbName;
	}

	public void usedb(String db) {
		this.dbName = db;
	}
	
	public void usetable(String table) {
		this.tableName = table;
	}
	public void setacc(String id, String pw) {
		this.id = id;
		this.pw = pw;
	}
	
	public void connect() {
		this.url = dbserver + dbName;
		try {
			c = DriverManager.getConnection(url, id, pw);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public String execute(String sql) {
		String err = null;
		try {
			Statement st;
			st = c.createStatement();
			
			st.executeUpdate(sql);
			
		} catch (SQLException e) {
			e.printStackTrace();
			
			err = e.getMessage();
		}
		
		return err;
	}
	public ResultSet query(String sql) {
		Statement st;
		ResultSet rs = null;
		String[][] result = null;
		int rowCount, colCount;
		try {
			st = c.createStatement();
			rs = st.executeQuery(sql);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	public String[][] query_str(String sql) {
		Statement st;
		String[][] result = null;
		int rowCount, colCount;
		try {
			st = c.createStatement();
			ResultSet rs = st.executeQuery(sql);
			
			rs.last();
			rowCount = rs.getRow();
			rs.first();
			colCount = rs.getMetaData().getColumnCount();
			result = new String[rowCount][colCount];
			for(int i=0;i<rowCount;i++) {
				for(int j=0;j<colCount;j++) {
					result[i][j] = rs.getString(j+1);
				}
				rs.next();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public void insert(String str) {
		
		String elm="";
		String[] splStr = str.split(",");
		for(String s : splStr) {
			boolean isString = false;
			for(int i=0;i<s.length();i++) {
				if(s.charAt(i) < '0' || s.charAt(i) > '9') {
					isString = true;
				}
			}
			if(isString)
				elm += "'"+s+"',";
			else
				elm += s;
		}
		elm = elm.substring(0,elm.length()-1);
		
		try {
			PreparedStatement ps = c.prepareStatement("INSERT INTO "+tableName+" VALUES("+str+")");
			ps.executeUpdate();
			
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void update(String a, String b, String cond) {
		Statement st;
		String sql = "UPDATE "+tableName+" SET "+a+" = '"+b+"' WHERE "+cond;
		try {
			st = c.createStatement();
			st.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void close() throws SQLException {
		c.close();
	}
	public int getCount(String sql) {
		Statement st;
		int cnt=0;
		try {
			ResultSet rs;
			st = c.createStatement();
			st.execute(sql);
			
			rs = st.getResultSet();
			rs.last();
			
			cnt = st.getResultSet().getRow();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return cnt;
	}
}
